<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
class Salary_model extends CI_Model{
    public function __construct() {
        parent::__construct();
        $this->load->database();
    }
    public function loadListSalary($fromdate,$todate)
    {
        $nghi='12:00:00';
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $query=$this->db->get('ewt_works');
        return $query;
    }
    public function loadListSalaryLimited($fromdate,$todate,$per_page,$start_row)
    {
        $nghi='12:00:00';
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $query=$this->db->get('ewt_works',$per_page,$start_row);
        return $query;
    }
    public function getDatabaseIDbyStaffID($Staff_ID)
    {
        $this->db->where('Staff_ID',$Staff_ID);
        $query=$this->db->get('Staff');
        return $query;
    }
   public function loadListSalaryStaff($Database_ID,$fromdate,$todate)
    {
        $nghi='12:00:00';
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->where('userid',$Database_ID);
        $query=$this->db->get('ewt_works');
        return $query;
    }
    public function loadListSalaryLimitedSalary($Database_ID,$fromdate,$todate,$per_page,$start_row)
    {
        $nghi='12:00:00';
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->where('userid',$Database_ID);
        $query=$this->db->get('ewt_works',$per_page,$start_row);
        return $query;
    }
    public function getNumberDay($fromdate,$todate)
    {
        $nghi='12:00:00';
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->select('userid,COUNT(userid) as numberday');
        $this->db->group_by('userid');
        //$this->db->distinct();
        $query=$this->db->get('ewt_works');
        return $query;
    }
    public function getNumberDayLimited($fromdate,$todate,$per_page,$start_row)
    {
        $nghi='12:00:00';
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->select('userid,COUNT(userid) as numberday');
        $this->db->group_by('userid');
        //$this->db->distinct();
        $query=$this->db->get('ewt_works',$per_page,$start_row);
        return $query;
    }
    public function getNumberDayStaff($Database_ID,$fromdate,$todate)
    {
        $nghi='12:00:00';
        $this->where('userid',$Database_ID);
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->select('userid,COUNT(starttime) as numberday');
        //$this->db->group_by('userid');
        //$this->db->distinct();
        $query=$this->db->get('ewt_works');
        return $query;
    }
    public function getFullNameByID($Staff_ID)
    {
        $this->db->where('Staff_ID',$Staff_ID);
        $query=$this->db->get('Staff');
        return $query;
    }
    public function getDatabaseIDLab($Lab_ID)
    {
        $this->db->where('Lab_ID',$Lab_ID);
        $query=$this->db->get('Staff');
        return $query;
    }
    public function getAllWorkDayLab($Lab_ID,$fromdate,$todate)
    {
        $nghi='12:00:00';
        $this->db->where_in("select Database_ID from Staff where Lab_ID='$Lab_ID'");
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->select('userid,COUNT(starttime) as numberday');
        $this->db->group_by('userid');
        $query=$this->db->get('ewt_works');
        return $query;
    }
    public function getAllWorkDayLabLimited($Lab_ID,$fromdate,$todate,$per_page,$start_row)
    {
        $nghi='12:00:00';
        $this->db->where_in("select Database_ID from Staff where Lab_ID='$Lab_ID'");
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->select('userid,COUNT(starttime) as numberday');
        $this->db->group_by('userid');
        $query=$this->db->get('ewt_works',$per_page,$start_row);
        return $query;
    }
    public function getAllWorkDayLabString($Lab_ID,$fromdate,$todate)
    {
        /*$nghi='12:00:00';
        $this->db->where_in('userid',$string);
        $this->db->where('starttime >=', $fromdate);
        $this->db->where('starttime <=', $todate);
        $this->db->where('substr(starttime,12,8)<>',$nghi);
        $this->db->select('userid,COUNT(starttime) as numberday');
        $this->db->group_by('userid');
        $query=$this->db->get('ewt_works');
        return $query;*/
        $nghi='12:00:00';
        $sql="select userid,COUNT(starttime) as numberday from ewt_works where userid IN (select Database_ID from Staff where Lab_ID='$Lab_ID') 
        and starttime between '$fromdate' and '$todate' and substr(starttime,12,8)<>'$nghi' group by userid";
        $query=$this->db->query($sql);
        return $query;
    }
}
?>
